package dao.impl;

import dao.UserDao;
import entity.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import util.DataSourceUtil;
import util.Util;

import java.sql.SQLException;
import java.util.List;

public class UserDaoImpl implements UserDao {

    public boolean isExist(String userId) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select count(*) from users where userId=?";
        Long count=runner.query(sql,new ScalarHandler<Long>(),userId);
        if(count>0){
            return true;
        }
        else {
            return false;
        }
    }

    public boolean addUser(User user) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="insert into users values(?,?,?,?,?,?,?,?)";
        Object[] params={user.getUserId(),user.getUserName(),user.getBirth(),user.getSex(),user.getAddress(),user.getUserPassword(),user.getType(),user.getRegdate()};
        int result= runner.update(sql,params);
        if(result>0){
            return true;
        }else{
            return false;
        }
    }

    public List<User> queryAllUsers() throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select * from users";
        return runner.query(sql,new BeanListHandler<User>(User.class));
    }

    public List<User> queryAllUsersByRequire(String sql,Object[] params) throws SQLException{
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        return runner.query(sql,new BeanListHandler<User>(User.class),params);
    }

    public User queryStudentById(String userId) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select * from users where userId=?";
        return runner.query(sql,new BeanHandler<User>(User.class),userId);
    }

    public boolean deleteUserById(String userId) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="delete from users where userId=?";
        int result=runner.update(sql,userId);
        if(result>0){
            return true;
        }
        else {
            return false;
        }
    }

    public boolean updateUserById(String userId, User user) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="update users set userName=?, birth=?, sex=? ,address=? ,userPassword=?where userId=?";
        Object[] params={user.getUserName(),user.getBirth(),user.getSex(),user.getAddress(),user.getUserPassword(),userId};
        int result=runner.update(sql,params);
        if(result>0){
            return true;
        }else{
            return false;
        }
    }

    public int getTotalCount(String sql,Object[] params) throws SQLException {
        return Util.getCountTotal(sql,params).intValue();
    }

    public List<User> queryUserByPage(int currentPage,int pageSize) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select * from users limit ?,?";
        Object[] params={currentPage*pageSize,pageSize};
        return runner.query(sql,new BeanListHandler<User>(User.class),params);
    }

    public List<User> queryUserByPageRequire(int currentPage,int pageSize,Object param) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select distinct * from users  where userId=? or userName=? limit ?,?";
        Object[] params={param,param,currentPage*pageSize,pageSize};
        return runner.query(sql,new BeanListHandler<User>(User.class),params);
    }

}
